-- tbl_comment_album


CREATE PROC CommentAlbum_GetAll
AS
	SELECT * FROM tbl_comment_album
GO

CREATE PROC CommentAlbum_GetInfo
@comment_id INT
AS 
	SELECT * FROM tbl_comment_album WHERE comment_id = @comment_id
GO

CREATE PROC CommentAlbum_Insert
@album_id INT,
@friend_id INT,
@text NVARCHAR(255),
@created_date DATETIME
AS 
	INSERT INTO tbl_comment_album(album_id, friend_id, text, created_date)
	VALUES (@album_id, @friend_id, @text, @created_date)
GO

ALTER PROC CommentAlbum_Insert
@album_id INT,
@friend_id INT,
@text NVARCHAR(255),
@created_date DATETIME,
@comment_id INT OUTPUT
AS 
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO tbl_comment_album(album_id, friend_id, text, created_date)
		VALUES (@album_id, @friend_id, @text, @created_date)
		SELECT @intErrorCode = @@ERROR, @comment_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO

CREATE PROC CommentAlbum_Update
@comment_id INT,
@album_id INT,
@friend_id INT,
@text NVARCHAR(255),
@created_date DATETIME
AS 
	UPDATE tbl_comment_album
	SET
		album_id = @album_id,
		friend_id = @friend_id,
		text = @text,
		created_date = @created_date
	WHERE comment_id = @comment_id
GO

CREATE PROC CommentAlbum_Delete
@comment_id INT
AS
	DELETE FROM tbl_comment_album
	WHERE comment_id = @comment_id
GO

-- tbl_album
CREATE PROC Album_GetAll
AS
	SELECT * FROM tbl_album
GO

CREATE PROC Album_GetInfo
@album_id INT
AS 
	SELECT * FROM tbl_album WHERE album_id = @album_id
GO

CREATE PROC Album_Insert
@user_id INT,
@album_name NVARCHAR(50),
@description NVARCHAR(255),
@created_date DATETIME,
@modified_date DATETIME,
@is_shown BIT,
@folder_path nvarchar(255),
@album_id INT OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	INSERT INTO tbl_album(user_id,album_name,description,created_date, modified_date,is_shown, folder_path)
	VALUES (@user_id,@album_name,@description,@created_date, @modified_date,@is_shown, @folder_path)
GO

ALTER PROC Album_Insert
@user_id INT,
@album_name NVARCHAR(50),
@description NVARCHAR(255),
@created_date DATETIME,
@modified_date DATETIME,
@is_shown BIT,
@folder_path nvarchar(255),
@album_id INT OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO tbl_album(user_id,album_name,description,created_date, modified_date,is_shown, folder_path)
		VALUES (@user_id,@album_name,@description,@created_date, @modified_date,@is_shown, @folder_path)
		SELECT @intErrorCode = @@ERROR, @album_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO

CREATE PROC Album_Update
@album_id INT,
@user_id INT,
@album_name NVARCHAR(50),
@description NVARCHAR(255),
@created_date DATETIME,
@modified_date DATETIME,
@is_shown BIT,
@folder_path nvarchar(255)
AS
	UPDATE tbl_album
	SET
		user_id = @user_id,
		album_name = @album_name,
		description = @description,
		created_date = @created_date,
		is_shown = @is_shown,
		folder_path = @folder_path
	WHERE album_id = @album_id
GO

CREATE PROC Album_Delete
@album_id INT
AS 
	DELETE FROM tbl_album
	WHERE album_id = @album_id
GO

-- tbl_user

CREATE PROC User_GetAll
AS
	SELECT * FROM tbl_user
GO

CREATE PROC User_GetInfo
@user_id INT
AS
	SELECT * FROM tbl_user
	WHERE user_id = @user_id
GO

CREATE PROC User_Insert
@username VARCHAR(32),
@password VARCHAR(32),
@name_first VARCHAR(50),
@name_last VARCHAR(50),
@email VARCHAR(100),
@avatar VARCHAR(255),
@active TINYINT,
@online TINYINT,
@created_date DATETIME,
@user_id INT OUTPUT
AS
	INSERT INTO tbl_user(username,password,name_first,name_last,avatar,active,online,created_date)
	VALUES(@username,@password,@name_first,@name_last,@avatar,@active,@online,@created_date)
	
GO

ALTER PROC User_Insert
@username VARCHAR(32),
@password VARCHAR(32),
@name_first VARCHAR(50),
@name_last VARCHAR(50),
@email VARCHAR(100),
@avatar VARCHAR(255),
@active TINYINT,
@online TINYINT,
@created_date DATETIME,
@user_id INT OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO tbl_user(username,password,name_first,name_last,avatar,active,online,created_date)
		VALUES(@username,@password,@name_first,@name_last,@avatar,@active,@online,@created_date)
		SELECT @intErrorCode = @@ERROR, @user_id = @@IDENTITY
	END
	RETURN @intErrorCode	
GO

CREATE PROC User_Update
@user_id INT,
@username VARCHAR(32),
@password VARCHAR(32),
@name_first VARCHAR(50),
@name_last VARCHAR(50),
@email VARCHAR(100),
@avatar VARCHAR(255),
@active TINYINT,
@online TINYINT,
@created_date DATETIME
AS
	UPDATE tbl_user
	SET
		username = @username,
		password = @password,
		name_first = @name_first,
		name_last = @name_last,
		email = @email,
		avatar = @avatar,
		active = @active,
		online = @online,
		created_date = @created_date
	WHERE user_id = @user_id
GO

CREATE PROC User_Delete
@user_id INT
AS
	DELETE FROM tbl_user
	WHERE user_id = @user_id
GO